package jdbc04;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import java.util.Scanner;

public class MyJdbcConnection {

	public static void main(String[] args) {
		try {
			loadDriverClass();
			System.out.println("============== Statement ===============");
			doStatement();
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		System.out.println("============== PreparedStatement ===============");
		doPerparedStatement();
		
		System.out.println("============== CallableStatement ===============");
		doCallableStatement();
	}
	
	private static void doCallableStatement() {
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;

		Scanner sc = new Scanner(System.in);
		try {
			conn = getConnection();
			stmt = conn.prepareCall("call besuper.get_player(?, ?)");
			
			System.out.println("name pattern: ");
			String namePattern = sc.nextLine();
			
			stmt.setString(1, namePattern);
			stmt.registerOutParameter(2, Types.INTEGER);
			stmt.execute();
			int resultCount = stmt.getInt(2);
			System.out.println("get total: " + resultCount);
			
			rs = stmt.getResultSet();

			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				String username = rs.getString("username");

				System.out.println("id: " + id + ", name: " + name + ", score: " + score + ", username: " + username);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
	}

	private static void doStatement() throws ClassNotFoundException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			//System.out.println("get connection: " + conn);

			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from besuper.player");

			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				String username = rs.getString("username");

				System.out.println("id: " + id + ", name: " + name + ", score: " + score + ", username: " + username);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
	}

	private static void doPerparedStatement() {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			stmt = conn.prepareStatement("select * from besuper.player where id = ?");
			
			stmt.setInt(1, 1);

			rs = stmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				String username = rs.getString("username");

				System.out.println("id: " + id + ", name: " + name + ", score: " + score + ", username: " + username);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
	}

	public static void clean(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static Connection getConnection() throws SQLException {
		Connection conn;
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");

		conn = DriverManager.getConnection(url, user, password);
		return conn;
	}

	private static void loadDriverClass() throws ClassNotFoundException {
		Class.forName(getProperty("jdbc.driver"));
	}

	private static String getProperty(String key) {
		Properties props = new Properties();
		try {
			props.load(new FileInputStream("conn.properties"));

			return props.getProperty(key);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return null;
	}

}
